package cn.com.cifi.mars.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import cn.com.cifi.mars.constant.Constants;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import cn.com.cifi.mars.bean.vo.supply.SupplyMonthlyBaseValueVO;
import cn.com.cifi.mars.bean.vo.supply.SupplyMonthlyDetailExcelVO;
import cn.com.cifi.mars.constant.OrgTypeEunm;

public class SupplyMonthlyDeatilReportTemplate {
    
        private static final String TEMPLATE_PATH = "/template/supply_monthly_detail_template.xls";

	    private HSSFWorkbook workbook;

	    private HSSFSheet sheet;

	    /**
	     * 默认sheet索引
	     */
	    private int defaultSheetIndex = 0;

	    /**
	     * 数据开始行
	     */
	    private int dataBeginRow = 3;

	    /**
	     * 当前行标
	     */
	    private int currentDataRowCursor = dataBeginRow;
	    
	    private static final String DATE_PATTERN = "{yearMonth}";
	    
	    /**
	     * 常规样式-不带底色
	     */
	    private HSSFCellStyle cellStyle1;
	    
	    /**
	     * 样式（数字格式-0位小数，居右）-不带底色
	     */
	    private HSSFCellStyle cellStyle2;
	    
	    /**
	     * 样式（数字格式-2位小数，居右）-不带底色
	     */
	    private HSSFCellStyle cellStyle3;
	    
	    /**
	     * 样式（百分比格式-1位小数，居右）-不带底色
	     */
	    private HSSFCellStyle cellStyle4;
	    
	    /**
	     * 样式（百分比格式-2位小数，居右）-不带底色
	     */
	    private HSSFCellStyle cellStyle5;
	    
	    /**
	     * 权益比例
	     */
	    private static final String EQUITY_RATIO  = "equityRatio";
	    /**
	     * 数字-两位小数
	     */
		private static final String NUMERIC_TWO  = "numericByTwo";
		/**
	     * 数字-0位小数
	     */
		private static final String NUMERIC_ZERO  = "numericByZero";
		/**
	     * 达成率
	     */
		private static final String ACHIEVING_RATE   = "achievingRate ";
		
		public static final BigDecimal NUM_10000 = new BigDecimal("10000");

	    public SupplyMonthlyDeatilReportTemplate(String deadline) {
	        try {
	            InputStream tempInputStream = SupplyMonthlyDeatilReportTemplate.class.getResourceAsStream(TEMPLATE_PATH);
	            if (tempInputStream != null) {
	                workbook = new HSSFWorkbook(tempInputStream);
	                sheet = workbook.getSheetAt(defaultSheetIndex);
	                setSheetDate(deadline);
	                setYearMonth(deadline);
	                //常规
	                cellStyle1=sheet.getRow(3).getCell(1).getCellStyle();
	                //数字格式-0位小数
	                cellStyle2=sheet.getRow(3).getCell(24).getCellStyle();
	                //数字格式-2位小数
	                cellStyle3=sheet.getRow(3).getCell(26).getCellStyle();
	                //百分比格式-1位小数
	                cellStyle4=sheet.getRow(3).getCell(50).getCellStyle();
	                //百分比格式-2位小数
	                cellStyle5=sheet.getRow(3).getCell(9).getCellStyle();
	            }
	        } catch (Exception e) {
	            throw new RuntimeException(e);
	        }
	    }

		public void setSheetDate(String deadline) {
	        workbook.setSheetName(defaultSheetIndex, workbook.getSheetName(defaultSheetIndex).replace("供货明细", "月度供货明细表-截止".concat(deadline.replace("-", "")).concat("-").concat(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHH")))));
	    }
		
		private void setYearMonth(String deadline) {
			HSSFRow row = sheet.getRow(0);
	        if (row != null) {
	            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
	            	HSSFCell cell = row.getCell(i);
	                if (cell != null && cell.getStringCellValue().contains(DATE_PATTERN)) {
	                    cell.setCellValue(cell.getStringCellValue().replace(DATE_PATTERN, deadline.split("-")[0]+"年"+deadline.split("-")[1]+"月"));
	                }
	            }
	        }
			
		}

	    /**
	     * 批量插入行
	     *
	     * @param list
	     */
	    public synchronized void appendRows(List<SupplyMonthlyDetailExcelVO> list) {
	    	for (SupplyMonthlyDetailExcelVO vo : list) {
	    		if(StrUtils.isEmpty(vo.getAreaName())) {
	    			continue;
	    		}
	            this.appendRow(vo);
	        }
	    }
	    
	    /**
	     * 插入列
	     *
	     * @param row
	     * @param index
	     * @param value
	     */
	    public synchronized void appendCell(HSSFRow row, int index, String value,String type) {
	    	HSSFCell cell = row.getCell(index);
	        if (cell == null) {
	            cell = row.createCell(index);
	        }
	        switch (type) {
			case NUMERIC_ZERO:
				cell.setCellStyle(cellStyle2);
				if(StrUtils.isNotEmpty(value)) {
					cell.setCellValue(Double.valueOf(value).doubleValue());
				}else {
					cell.setCellValue(value);
				}
				break;
			case NUMERIC_TWO:
				cell.setCellStyle(cellStyle3);
				if(StrUtils.isNotEmpty(value)) {
					cell.setCellValue(Double.valueOf(value).doubleValue());
				}else {
					cell.setCellValue(value);
				}
				break;
			case ACHIEVING_RATE:
				cell.setCellStyle(cellStyle4);
				if(StrUtils.isNotEmpty(value) && !value.equals("-")) {
					cell.setCellValue(Double.valueOf(value).doubleValue());
				}else {
					cell.setCellValue(value);
				}
				break;
		    case EQUITY_RATIO:
				cell.setCellStyle(cellStyle5);
				if(StrUtils.isNotEmpty(value)) {
					double dValue = Double.valueOf(NumberUtils.divide(value, "100", 4)).doubleValue();
					cell.setCellValue(dValue);
    			}else {
                	cell.setCellValue(value);
    			}
				break;
			default:
				cell.setCellStyle(cellStyle1);
				cell.setCellValue(value);
				break;
			}
	    }
	    
	    /**
	     * 插入行
	     *
	     * @param map
	     */
	    public synchronized void appendRow(SupplyMonthlyDetailExcelVO vo) {
	    	HSSFRow row = sheet.getRow(currentDataRowCursor);
	        if (row == null) {
	            row = sheet.createRow(currentDataRowCursor);
	        }
	        row.setHeightInPoints(Constants.HEIGHT_POINTS);
	        AtomicInteger index = new AtomicInteger(-1);
	        //版本类型
	        this.appendCell(row, index.addAndGet(1),vo.getVersionType(),"");
	        //层级
	        this.appendCell(row, index.addAndGet(1),OrgTypeEunm.getValueByKey(vo.getType()),"");
	        //区域
	        this.appendCell(row, index.addAndGet(1),vo.getAreaName(),"");
	        //城市公司
	        this.appendCell(row, index.addAndGet(1),vo.getCityName(),"");
	        //项目信息
	        this.appendCell(row, index.addAndGet(1),vo.getProjectName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProjectCode(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getIsFirst(),"");
	        //分期信息
	        this.appendCell(row, index.addAndGet(1),vo.getStageName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getStageCode(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getEquityRatio(),EQUITY_RATIO);
	        this.appendCell(row, index.addAndGet(1),vo.getTradersWay(),"");
	        this.appendCell(row, index.addAndGet(1),handleIsMergeTable(vo.getIsMergeTable()),"");
	        this.appendCell(row, index.addAndGet(1),handlePendingHomeSale(vo.getPendingHomeSale()),"");
	        //组团信息
	        this.appendCell(row, index.addAndGet(1),vo.getGroupName(),"");
	        //产品信息
	        this.appendCell(row, index.addAndGet(1),vo.getProductTypeName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductTypeCode(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductCode(),"");
	        //楼栋信息
	        this.appendCell(row, index.addAndGet(1),vo.getBulidNo(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getBulidNo2(),"");
	        this.appendCell(row, index.addAndGet(1),handlePendingHomeSale(vo.getPendingHomeSale2()),"");
	        this.appendCell(row, index.addAndGet(1),vo.getBusinessTypeName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getFreeTypeName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProTypeName(),"");
	        //月初存货
	        SupplyMonthlyBaseValueVO inventory = vo.getInventory();
	        if(inventory != null) {
	        	this.appendCell(row, index.addAndGet(1),handNum(inventory.getRoomNum()),NUMERIC_ZERO);
        		this.appendCell(row, index.addAndGet(1),handNum(inventory.getParkingNum()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(inventory.getSaleArea()),NUMERIC_TWO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(inventory.getHouseAvgPrice()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(inventory.getParkingAvgPrice()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(NumberUtils.BigDecimalADD(2, inventory.getHousePrice(), inventory.getParkingPrice()))),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(inventory.getHousePrice())),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(inventory.getParkingPrice())),NUMERIC_ZERO);
	        }
	        //计划
	        SupplyMonthlyBaseValueVO plan = vo.getPlan();
	        if(plan != null) {
	        	this.appendCell(row, index.addAndGet(1),handNum(plan.getRoomNum()),NUMERIC_ZERO);
        		this.appendCell(row, index.addAndGet(1),handNum(plan.getParkingNum()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(plan.getSaleArea()),NUMERIC_TWO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(plan.getHouseAvgPrice()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(plan.getParkingAvgPrice()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(NumberUtils.BigDecimalADD(2, plan.getHousePrice(), plan.getParkingPrice()))),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(plan.getHousePrice())),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(plan.getParkingPrice())),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),plan.getSupplyDate(),"");
	        }
	        //实际
	        SupplyMonthlyBaseValueVO actual = vo.getActual();
	        if(actual != null) {
	        	this.appendCell(row, index.addAndGet(1),handNum(actual.getRoomNum()),NUMERIC_ZERO);
        		this.appendCell(row, index.addAndGet(1),handNum(actual.getParkingNum()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(actual.getSaleArea()),NUMERIC_TWO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(actual.getHouseAvgPrice()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(actual.getParkingAvgPrice()),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(NumberUtils.BigDecimalADD(2, actual.getHousePrice(), actual.getParkingPrice()))),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(actual.getHousePrice())),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),ReportUtils.bigDecimalToString(handlePrice(actual.getParkingPrice())),NUMERIC_ZERO);
		        this.appendCell(row, index.addAndGet(1),actual.getSupplyDate(),"");
	        }
	        
	        //达成率=实际货值/计划货值
			BigDecimal sumPriceActual = actual == null ? null :actual.getTotalPrice();
			BigDecimal sumPrice = plan == null ? null : plan.getTotalPrice();
			if(sumPrice == null || sumPrice.compareTo(BigDecimal.ZERO) == 0) {
				this.appendCell(row, index.addAndGet(1),"-",ACHIEVING_RATE);
			}else {
				this.appendCell(row, index.addAndGet(1),NumberUtils.divide(sumPriceActual, sumPrice, 4)+"",ACHIEVING_RATE);
			}
	        this.appendCell(row, index.addAndGet(1),"","");
	        currentDataRowCursor++;
	    }

	    /**
	     * 导出
	     *
	     * @param outputStream
	     * @throws IOException
	     */
	    public void write(OutputStream outputStream) throws IOException {
	        workbook.write(outputStream);
	    }
	    
	    private String handleIsMergeTable(String value) {
	    	if(StrUtils.isEmpty(value)) {
	    		return "";
	    	}
	    	return value.equals("0") ? "否" : "是";
	    }
	    
	    private String handlePendingHomeSale(String value) {
	    	if(StrUtils.isEmpty(value)) {
	    		return "";
	    	}
	    	return value.equals("0") ? "非现房销售" : "现房销售";
	    }
	    
	    public static BigDecimal handlePrice(BigDecimal price) {
			if(price == null) {
				return BigDecimal.ZERO;
			}
			BigDecimal value = NumberUtils.divide(price,NUM_10000,0);
			return value;
		}
	    
	    public static String handNum(Integer num) {
	    	if(num != null) {
	    		return String.valueOf(num);
	    	}
	    	return "0";
	    }
	    
	    
}
